package net.admin.db.qna;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class QnADAO {
	
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public QnADAO(){
		try{
			Context initCtx = new InitialContext();
			Context envCtx = (Context)initCtx.lookup("java:comp/env");
			DataSource ds = (DataSource)envCtx.lookup("jdbc/OracleDB");
			con = ds.getConnection();
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	
	public void writeQnA(QnABean article) throws Exception{
		
		int num=article.getNum();
		int ref=article.getRef();
		int re_step=article.getRe_step();
		int re_level=article.getRe_level();
		int number=0;
        String sql="";
        
        try{
        	pstmt = con.prepareStatement("select max(num) from QNA");
        	rs = pstmt.executeQuery();
        	
        	if(rs.next()){
        		number=rs.getInt(1)+1;
        	}else{
        		number=1;
        	}
        	
        	if(num!=0){
        		sql = "update QNA set re_step=re_step+1 where ref= ? and re_step> ?";
        		pstmt = con.prepareStatement(sql);
        		pstmt.setInt(1, ref);
        		pstmt.setInt(2, re_step);
        		pstmt.executeUpdate();
        		re_step=re_step+1;
        		re_level=re_level+1;
        	}else{
        		ref=number;
        		re_step=0;
        		re_level=0;
        	}
        	
            sql = "insert into QNA(num,writer,email,subject,password,reg_date,";
            sql+="ref,re_step,re_level,content,ip) values(QNA_NUM.NEXTVAL,?,?,?,?,?,?,?,?,?,?)";

            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, article.getWriter());
            pstmt.setString(2, article.getEmail());
            pstmt.setString(3, article.getSubject());
            pstmt.setString(4, article.getPassword());
            pstmt.setTimestamp(5, article.getReg_date());
            pstmt.setInt(6, ref);
            pstmt.setInt(7, re_step);
            pstmt.setInt(8, re_level);
            pstmt.setString(9, article.getContent());
            pstmt.setString(10, article.getIp());

            pstmt.executeUpdate();
        	
        }catch(Exception ex){
        	ex.printStackTrace();
        }finally{
        	
        }
        
	}
	
	public int getQnACount() throws Exception{
		int x=0;
		
		try{
			pstmt = con.prepareStatement("select count(*) from QNA");
			rs = pstmt.executeQuery();
			if(rs.next()){
				x=rs.getInt(1);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return x;
	}
	
	public List getQnAs(int start, int end) throws Exception{
		List articleList=null;
		try{
			pstmt = con.prepareStatement(
		            "select num,writer,email,subject,password,reg_date,ref,re_step,re_level,content,ip,readcount,r  " +
		            "from (select num,writer,email,subject,password,reg_date,ref,re_step,re_level,content,ip,readcount,rownum r " +
		            "from (select num,writer,email,subject,password,reg_date,ref,re_step,re_level,content,ip,readcount " +
		            "from QNA order by ref desc, re_step asc) order by ref desc, re_step asc ) where r >= ? and r <= ? ");
			pstmt.setInt(1, start);
			pstmt.setInt(2, end);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				articleList = new ArrayList();
				do{
					QnABean article = new QnABean();
					
					article.setNum(rs.getInt("num"));
					article.setWriter(rs.getString("writer"));
					article.setEmail(rs.getString("email"));
					article.setSubject(rs.getString("subject"));
					article.setPassword(rs.getString("password"));
					article.setReg_date(rs.getTimestamp("reg_date"));
					article.setReadcount(rs.getInt("readcount"));
					article.setRef(rs.getInt("ref"));
					article.setRe_step(rs.getInt("re_step"));
					article.setRe_level(rs.getInt("re_level"));
					article.setContent(rs.getString("content"));
					article.setIp(rs.getString("ip"));
					
					articleList.add(article);
				}while(rs.next());
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			
		}
		return articleList;
	}
	
	public QnABean getQnA(int num) throws Exception{
		QnABean article=null;
		try{
			pstmt = con.prepareStatement("update QNA set readcount=readcount+1 where num = ?");
			pstmt.setInt(1, num);
			pstmt.executeUpdate();
			
			pstmt = con.prepareStatement("select * from QNA where num = ?");
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				article = new QnABean();
				article.setNum(rs.getInt("num"));
				article.setWriter(rs.getString("writer"));
				article.setEmail(rs.getString("email"));
				article.setSubject(rs.getString("subject"));
				article.setPassword(rs.getString("password"));
				article.setReg_date(rs.getTimestamp("reg_date"));
                article.setReadcount(rs.getInt("readcount"));
                article.setRef(rs.getInt("ref"));
                article.setRe_step(rs.getInt("re_step"));
                article.setRe_level(rs.getInt("re_level"));
                article.setContent(rs.getString("content"));
                article.setIp(rs.getString("ip")); 
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			
		}
		return article;
	}

	
}